package com.situ.day4;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

import com.situ.day2.JDBCUtilEnd;
import com.situ.day3.StudentDome1;

public class Shiwu {
	@Test
	public void test() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		String sql1 = "UPDATE zhuanzhang SET money = money - 1000 WHERE NAME = '张三';";
		String sql2 = "UPDATE zhuanzhang SET money = money + 1000 WHERE NAME = '李四';";
		try {
			connection = JDBCUtilEnd.getConnection();
			connection.setAutoCommit(false);
			preparedStatement = connection.prepareStatement(sql1);
			preparedStatement.executeUpdate();
//			int i = 3 / 0;
			preparedStatement = connection.prepareStatement(sql2);
			preparedStatement.executeUpdate();
			connection.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}finally {
			JDBCUtilEnd.close(connection, preparedStatement, null);
		}
	}
	
	@Test  //调用存储过程
	public void test3() {
	Connection connection = null;
	CallableStatement callableStatement = null;
	ResultSet resultSet  =null;
	try {
		connection = JDBCUtilEnd.getConnection();
		callableStatement = connection.prepareCall("CALL show_student");
		resultSet = callableStatement.executeQuery();
		while (resultSet.next()) {
			Integer id = resultSet.getInt("id");
			String  name = resultSet.getString("name");
			Integer age = resultSet.getInt("age");
			String gender = resultSet.getString("gender");
			StudentDome1 studentDome1 = new StudentDome1(id,name,age,gender);
			System.out.println(studentDome1);
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}
	}
	
	@Test
	public void test4() {
		Connection connection  = null;
		CallableStatement callableStatement = null;
		ResultSet resultSet = null;
		try {
			connection = JDBCUtilEnd.getConnection();
			callableStatement = connection.prepareCall("CALL show_student");
			resultSet = callableStatement.executeQuery();
			while (resultSet.next()) {
				System.out.println(resultSet.getObject(1)+"   "+resultSet.getObject(2)+"   "+resultSet.getObject(3)+"   "+resultSet.getObject(4)+"   "+resultSet.getObject(5));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
